package util;

import java.io.PrintWriter;
import java.io.StringWriter;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

/**
 * @program: myProject
 * @description: 静态调动的jdbc方法工具类
 * @author: 淡梦如烟
 * @create: 2019-09-03 15:07
 */
public class StaticDateBaseConnUtil {

    /**
     * 数据库地址
     */
    private String DBurl = "jdbc:mysql://127.0.0.1:3306/jeesite?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=UTC";
    /**
     * 数据库用户名
     */
    private String user = "aaa";
    /**
     * 数据库密码
     */
    private String password = "aaa";
    /**
     * 数据库连接
     */
    private Connection conn = null;
    /**
     * sql请求参数
     */
    private PreparedStatement ps = null;
    /**
     * sql返回的结果集
     */
    private ResultSet rs = null;
    /**
     * 本实例
     */
    private static StaticDateBaseConnUtil staticDateBaseConnUtil = null;
    /**
     * 日志对象
     */
    private Logger logger = Logger.getLogger(this.getClass().getName());


    /**
     * 获取数据库连接实例
     *
     * @return 实例
     */
    public static StaticDateBaseConnUtil getInstance() {
        if (staticDateBaseConnUtil == null) {
            staticDateBaseConnUtil = new StaticDateBaseConnUtil();
        }
        return staticDateBaseConnUtil;
    }

    /**
     * 获取自定义数据库连接实例
     *
     * @param DBurl    数据库地址
     * @param user     数据库用户名
     * @param password 数据库密码
     * @return
     */
    public static StaticDateBaseConnUtil getInstance(String DBurl, String user, String password) {
//        if(staticDateBaseConnUtil==null){
        staticDateBaseConnUtil = new StaticDateBaseConnUtil(DBurl, user, password);
//        }
        return staticDateBaseConnUtil;
    }

    /**
     * 构造函数 新建自定义mysql连接
     *
     * @param DBurl    数据库地址
     * @param user     数据库用户名
     * @param password 数据库密码
     */
    private StaticDateBaseConnUtil(String DBurl, String user, String password) {
        logger.info("自定义mysql连接。\n数据库地址[" + DBurl + "]\n数据库用户名[" + user + "]\n数据库密码[" + password + "]");
        this.DBurl = DBurl;
        this.user = user;
        this.password = password;
    }

    /**
     * 构造函数 新建自定义mysql连接
     *
     * @param dBurl   数据库地址
     * @param dBuser  数据库用户名
     * @param dBpsw   数据库密码
     * @param testSql 测试sql
     */
    public StaticDateBaseConnUtil(String dBurl, String dBuser, String dBpsw, String testSql) throws SQLException {
        logger.info("自定义mysql连接。\n数据库地址[" + dBurl + "]\n数据库用户名[" + dBuser + "]\n数据库密码[" + dBpsw + "]");
        this.DBurl = dBurl;
        this.user = dBuser;
        this.password = dBpsw;
        List<Map> list = select(testSql);
        if (list == null || list.size() == 0) {
            throw new SQLException("测试SQL[" + testSql + "]执行失败,连接测试失败！");
        }
    }

    /**
     * 建立连接
     *
     * @return Connection连接
     */
    public Connection getConnection() throws SQLException {
        if (null == conn || conn.isClosed()) {
            try {
//                Class.forName("com.mysql.jdbc.Driver");
                Class.forName("com.mysql.cj.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            try {
                conn = DriverManager.getConnection(DBurl, user, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }

    /**
     * 实现增删改
     *
     * @param sql     预编译sql语句
     * @param objects 参数
     * @return 是否成功的结果
     */
    public synchronized int update(String sql, Object... objects) {
        return edit(sql, false, objects);
    }

    /**
     * 实现增删改
     *
     * @param sql     预编译sql语句
     * @param close   是否关闭连接 可手动closeAll()关闭
     * @param objects 参数
     * @return 是否成功的结果
     */
    public int edit(String sql, Boolean close, Object... objects) {
        Integer rows = 0;
        Long time1 = System.currentTimeMillis();
        String text = "更改";
        try {
            sql = this.fillterSQL(sql);
            if (sql.trim().substring(0, 15).toLowerCase().contains("insert")) {
                text = "插入";
            }
            this.getConnection();
            ps = conn.prepareStatement(sql);
            StringBuffer pamas = new StringBuffer();
            if (objects != null && objects.length > 0) {
                //添加参数
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject(i + 1, objects[i]);
                    pamas.append(this.subStringPamas(objects[i]));
                }
                logger.info("SQL编译参数:" + pamas.substring(0, pamas.length() - 1).toString());
            }
            rows = ps.executeUpdate();
            Long time2 = System.currentTimeMillis();
            Long time3 = (time2 - time1) / 1000;
            Long time4 = (time2 - time1) % 1000;
            String time = time3 + "." + time4;
            if (rows > 0) {
                logger.info("SQL执行成功!耗时" + time + "秒;成功" + text + "了" + rows + "条数。");
            } else {
                logger.info("SQL执行成功,但未执行任何修改。");
            }
        } catch (Exception e) {
//           e.printStackTrace();
            this.getExceptionString(e);
        } finally {
            if (close) {
                this.closeAll();
            }
        }
        return rows;
    }

    /**
     * 省略参数
     *
     * @param object 参数对象
     * @return 省略的日志信息
     */
    private String subStringPamas(Object object) {
        String back = String.valueOf(object);
        if (back.length() > 100) {
            back = back.substring(0, 100) + " .....";
        }
        back = back + ",";
        return back;
    }

    /**
     * 查询数据 默认不关闭
     *
     * @param sql     预编译sql语句
     * @param objects 参数
     * @return
     */
    public synchronized List<Map> select(String sql, Object... objects) {
        return select(sql, false, objects);
    }

    /**
     * 查询数据
     *
     * @param sql     预编译sql语句
     * @param close   是否关闭连接 可手动closeAll()关闭
     * @param objects 参数
     * @return
     */
    public synchronized List<Map> select(String sql, Boolean close, Object... objects) {
        List list = new ArrayList();
        Long time1 = System.currentTimeMillis();
        try {
            sql = this.fillterSQL(sql);
            this.getConnection();
            ps = conn.prepareStatement(sql);
            StringBuffer pamas = new StringBuffer();
            if (objects != null && objects.length > 0) {
                //添加参数
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject(i + 1, objects[i]);
                    pamas.append(objects[i] + ",");
                }
                logger.info("SQL查询参数:" + pamas.substring(0, pamas.length() - 1).toString());
            }
            rs = ps.executeQuery();
            //获取键名
            ResultSetMetaData md = rs.getMetaData();
            //获取行的数量
            int columnCount = md.getColumnCount();
            while (rs.next()) {
                //声明Map
                Map rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    //获取键名及值
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
                list.add(rowData);
            }
            Long time2 = System.currentTimeMillis();
            Long time3 = (time2 - time1) / 1000;
            Long time4 = (time2 - time1) % 1000;
            String time = time3 + "." + time4;
            if (list.toString().length() > 200) {
                String info = list.toString().substring(0, 200);
                logger.info("SQL查询耗时" + time + "秒;返回结果" + list.size() + "条;简略信息:" + info + " .....");
            } else {
                logger.info("SQL查询耗时" + time + "秒;返回结果:" + list.toString());
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (close) {
                this.closeAll();
            }
        }
        return null;
    }

    /**
     * sql替换和过滤
     *
     * @param sql sql
     * @return sql
     */
    private String fillterSQL(String sql) {
        String sql2 = sql.replaceAll("\t", " ").replaceAll("\n", " ");
        logger.info("\n执行的预编译SQL是:" + sql2 + ";");
        return sql2;
    }

    /**
     * 关闭资源
     */
    public void closeAll() {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private StaticDateBaseConnUtil() {
    }

    /**
     * 打印中文的sql报错信息
     *
     * @param e
     * @return
     */
    public String getExceptionString(Exception e) {
        if (e == null) {
            return "";
        }
        StringWriter stringWriter = new StringWriter();
        e.printStackTrace(new PrintWriter(stringWriter));
        String back = stringWriter.toString();
        try {
            byte[] ab = back.getBytes("ISO8859-1");
            back = new String(ab, "utf-8");
        } catch (UnsupportedEncodingException e2) {
            e2.printStackTrace();
        }
        System.err.println(back);
        return back;
    }
}